package com.beamfield.common;

import java.beans.PropertyDescriptor;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelExport {

    public static Workbook create(InputStream in) throws
            IOException,InvalidFormatException {
        if (!in.markSupported()) {
            in = new PushbackInputStream(in, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(in)) {
            return new HSSFWorkbook(in);
        }
        if (POIXMLDocument.hasOOXMLHeader(in)) {
            return new XSSFWorkbook(OPCPackage.open(in));
        }
        throw new IllegalArgumentException("你的excel版本目前poi解析不了");
    }

	/**
	 * 根据模板导出excel
	 * @param title 标题
	 * @param templateMap key对应excel中的#key value 需要赋的值
	 * @param columnNames 列名
	 * @param objectList 对象列表
	 * @param templatePath 模板文件路径
	 * @param methodName 需要导出的方法名 注解的export中存在才会导出
	 * @param response
	 */
	public static void exportExcelByTemplate(String title, Map<String,String> templateMap, List<?> objectList,String columnNames[], String templatePath, HttpServletResponse response, String methodName) throws IOException {
        File templateExcel = new File(ExcelUtil.class.getClassLoader().getResource("").getPath().replace("classes/","")+"exceltemplate/"+templatePath);
        InputStream fis = null;
        Workbook workbook = null;
        Sheet sheet = null;
        try {
            // 将excel文件转为输入流
            fis = new FileInputStream(templateExcel);
            // 创建个workbook，
            workbook = create(fis);
            // 获取第一个sheet
            sheet = workbook.getSheetAt(0);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (sheet != null) {
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 0; i <= lastRowNum; i++) {
                Row curRow = sheet.getRow(i);
                for (int j = 0; j < curRow.getLastCellNum(); j++) {
                    Cell curCell = curRow.getCell(j);
                    String curValue = ExcelUtil.getCellValueAsString(curCell);
                    if (StringUtils.isNotEmpty(curValue) && templateMap.containsKey(curValue)) {
                        curCell.setCellValue(templateMap.get(curValue));
                    }
                    if (StringUtils.isNotEmpty(curValue) && curValue.contains("title")) {
                        curCell.setCellValue(title);
                    }
                }
            }
            Row listTitleRow = sheet.createRow(lastRowNum+1);
            CellStyle listTileCs = getCellStyle(workbook, (short) 14);
            listTileCs.setBorderBottom(CellStyle.BORDER_THIN);
            listTileCs.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            listTileCs.setBorderLeft(CellStyle.BORDER_THIN);
            listTileCs.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            listTileCs.setBorderRight(CellStyle.BORDER_THIN);
            listTileCs.setRightBorderColor(IndexedColors.BLACK.getIndex());
            listTileCs.setBorderTop(CellStyle.BORDER_THIN);
            listTileCs.setTopBorderColor(IndexedColors.BLACK.getIndex());
            //listTitleRow.setRowStyle(listTileCs);
            if(columnNames != null) {
                for (int i = 0; i < columnNames.length; i++) {
                    Cell cell = listTitleRow.createCell(i);
                    cell.setCellValue(columnNames[i]);
                    cell.setCellStyle(listTileCs);
                }
            }
            CellStyle listCs = getCellStyle(workbook, (short) 10);
            listCs.setBorderBottom(CellStyle.BORDER_THIN);
            listCs.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            listCs.setBorderLeft(CellStyle.BORDER_THIN);
            listCs.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            listCs.setBorderRight(CellStyle.BORDER_THIN);
            listCs.setRightBorderColor(IndexedColors.BLACK.getIndex());
            listCs.setBorderTop(CellStyle.BORDER_THIN);
            listCs.setTopBorderColor(IndexedColors.BLACK.getIndex());
            if(objectList != null) {
                for (int i = 0, j = lastRowNum + 2; i < objectList.size(); i++, j++) {
                    Row dataRow = sheet.createRow(j);
                    Object entity = objectList.get(i);
                    Field[] datafields = entity.getClass().getDeclaredFields();
                    Class<?> superclass = entity.getClass().getSuperclass();
                    if (superclass != null) {
                        Field[] superclassDeclaredFields = superclass.getDeclaredFields();
                        datafields = ArrayUtils.addAll(datafields, superclassDeclaredFields);
                    }
                    for (Field f : datafields) {
                        ExcelAnnotation eannotation = f.getAnnotation(ExcelAnnotation.class);
                        if (eannotation != null) {
                            String export = eannotation.export();
                            boolean shouldExportCurField = true;
                            if (StringUtils.isNotEmpty(export) && !export.contains(methodName)) {
                                shouldExportCurField = false;
                            }
                            if (shouldExportCurField) {
                                int sort = CommonUtils.parseInt(eannotation.sort(), 0);
                                try {
                                    PropertyDescriptor pd = new PropertyDescriptor(f.getName(), entity.getClass());
                                    //获得get方法
                                    Method getMethod = pd.getReadMethod();
                                    if (getMethod != null) {
                                        //执行get方法返回一个Object
                                        Object o = getMethod.invoke(entity);
                                        Cell cell = dataRow.createCell(sort);
                                        cell.setCellStyle(listCs);
                                        cell.setCellValue(String.valueOf(o == null ? "" : String.valueOf(o)));
                                    }
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                    }
                }
            }
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
            OutputStream os = null;
            try {
                os = response.getOutputStream();
                workbook.write(os);
                os.flush();
                os.close();
            } catch (Exception e) {
                // TODO: handle exception
            }
        }
    }
    public static CellStyle getCellStyle(Workbook workbook, short fh) {
        Font font = workbook.createFont();
        font.setFontHeightInPoints(fh);
        font.setFontName("微软雅黑");
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setFont(font);
        return cellStyle;
    }

	/**
	 *
	 * @param response
	 * @param objectlist 数据列表
	 * @param controllername  controllername， 需要跟注解的export 对应
	 * @param excelname 导出的名称
	 */
	public static void downloadExcel(HttpServletResponse response,List<?> objectlist,String controllername,String excelname){
		response.setCharacterEncoding("utf-8");
		response.setContentType("multipart/form-data");
		try {
			response.setHeader("Content-Disposition", "attachment;fileName="+java.net.URLEncoder.encode(excelname, "UTF-8")+".xls");
		} catch (UnsupportedEncodingException e1) {
			// TODO Auto-generated catch block
			response.setHeader("Content-Disposition", "attachment;fileName="+excelname+".xls");
		}
		OutputStream os = null;
		@SuppressWarnings("resource")
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(excelname);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        // 第四步，创建单元格，并设置值表头 设置表头居中
        CellStyle listTileCs = getCellStyle(wb, (short) 14);
        listTileCs.setBorderBottom(CellStyle.BORDER_THIN);
        listTileCs.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        listTileCs.setBorderLeft(CellStyle.BORDER_THIN);
        listTileCs.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        listTileCs.setBorderRight(CellStyle.BORDER_THIN);
        listTileCs.setRightBorderColor(IndexedColors.BLACK.getIndex());
        listTileCs.setBorderTop(CellStyle.BORDER_THIN);
        listTileCs.setTopBorderColor(IndexedColors.BLACK.getIndex());

        CellStyle listCs = getCellStyle(wb, (short) 10);
        listCs.setBorderBottom(CellStyle.BORDER_THIN);
        listCs.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        listCs.setBorderLeft(CellStyle.BORDER_THIN);
        listCs.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        listCs.setBorderRight(CellStyle.BORDER_THIN);
        listCs.setRightBorderColor(IndexedColors.BLACK.getIndex());
        listCs.setBorderTop(CellStyle.BORDER_THIN);
        listCs.setTopBorderColor(IndexedColors.BLACK.getIndex());

        HSSFRow datarow;
        HSSFRow row = sheet.createRow(0);
        HSSFCell titlecell,datacell;
        HSSFCellStyle cellStyle=wb.createCellStyle();
        cellStyle.setWrapText(true);
        for (int i=0;i<objectlist.size();i++){
          datarow = sheet.createRow(i+1);
	      Object entity = objectlist.get(i);
	      Field[] datafields = entity.getClass().getDeclaredFields();
            Class<?> superclass = entity.getClass().getSuperclass();
            if (superclass != null) {
                Field[] superclassDeclaredFields = superclass.getDeclaredFields();
                datafields = ArrayUtils.addAll(datafields, superclassDeclaredFields);
            }
		  for(Field f : datafields){
			  ExcelAnnotation eannotation = f.getAnnotation(ExcelAnnotation.class);
			  if(eannotation!=null){
				  String export = eannotation.export();
				  if(!CommonUtils.isEmptyString(export)){
					  String[] exports = export.split(",");
					  for(int j=0;j<exports.length;j++){
						  if(exports[j].contains(controllername)){
							  int sort=CommonUtils.parseInt(eannotation.sort().split(",")[j], 0);
							  if(i==0){
								  String title = eannotation.title().split(",")[j];
                                  sheet.setColumnWidth(sort, title.getBytes().length*2*256);
								  titlecell = row.createCell(sort);
								  titlecell.setCellValue(title);
								  titlecell.setCellStyle(listTileCs);
							  }
							  try{
								  PropertyDescriptor pd = new PropertyDescriptor(f.getName(),entity.getClass());
						          Method getMethod = pd.getReadMethod();//获得get方法
                                  if (getMethod != null) {
                                      //执行get方法返回一个Object
                                      Object o = getMethod.invoke(entity);
                                      if(o instanceof Date){
                                          o = DateFormatUtils.format((Date) o, "yyyy-MM-dd");
                                      }
                                      Cell cell = datarow.createCell(sort);
                                      cell.setCellStyle(listCs);
                                      cell.setCellValue(String.valueOf(o == null ? "" : String.valueOf(o)));
                                  }
							  }catch(Exception e){
								  e.printStackTrace();
							  }
        				  }
        			  }
        		  }
        	  }
    	  }
	    }
        try {
        	os = response.getOutputStream();
        	wb.write(os);
    		os.flush();
    		os.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
	}

}
